-- @owner: opentestcase023
-- @date: 2022/02/24
-- @testpoint: 在src表中插入数据时将计数器数字插入des表中之后将新值插入值des表中，更新数据时更新des表中id1=200，删除数据时删除des表中id2=2的行 each row

--step1:建表;expect:成功
drop table if exists t_trigger_src_00069 cascade;
drop table if exists t_trigger_des_00069 cascade;
create table t_trigger_src_00069(id1 int, id2 int, id3 int) with (storage_type=ustore);
create table t_trigger_des_00069(id1 int, id2 int, id3 int) with (storage_type=ustore);

--step2:在src表中插入数据时将计数器数字插入des表中之后将新值插入值des表中，
--更新数据时更新des表中id1=200，删除数据时删除des表中id2=2的行 each row;expect:成功
create or replace function ainsert_func()
returns trigger as $$
begin
if(tg_op='DELETE')
then
delete from t_trigger_des_00069 where id2=2;
return new;
end if;
if(tg_op='INSERT')
then
insert into t_trigger_des_00069 values(new.id1,new.id2,new.id3);
return new;
end if;
if(tg_op='UPDATE')
then
update t_trigger_des_00069 set id1=200;
return new;
end if;
return null;
end;
$$language plpgsql;
/
drop trigger if exists atrigger on t_trigger_src_00069;
create trigger atrigger after delete or insert or update on t_trigger_src_00069
for each row
execute procedure ainsert_func();
/
drop sequence if exists t_trigger_seq1;
create sequence t_trigger_seq1;
create or replace function binsert_func()
returns trigger as $$
begin
if(tg_op='DELETE')
then
delete from t_trigger_des_00069 where id3=3;
return new;
end if;
if(tg_op='INSERT')
then
insert into t_trigger_des_00069 values(nextval('t_trigger_seq1'),nextval('t_trigger_seq1'),nextval('t_trigger_seq1'));
return new;
end if;
if(tg_op='UPDATE')
then
update t_trigger_des_00069 set id1=300;
return new;
end if;
return null;
end;
$$language plpgsql;
/
drop trigger if exists btrigger on t_trigger_src_00069;
create trigger btrigger before delete or insert or update on t_trigger_src_00069
for each row
execute procedure binsert_func();
/
insert into t_trigger_src_00069 values (1,1,1),(2,2,2),(3,3,3);
select * from t_trigger_src_00069;
select * from t_trigger_des_00069;
update t_trigger_src_00069 set id1=100;
select * from t_trigger_src_00069;
select * from t_trigger_des_00069;
delete from t_trigger_src_00069 where id1=100;
select * from t_trigger_src_00069;
select * from t_trigger_des_00069;

--step3:清理环境;expect:成功
drop trigger if exists btrigger on t_trigger_src_00069;
drop table if exists t_trigger_src_00069 cascade;
drop table if exists t_trigger_des_00069 cascade;
drop sequence if exists t_trigger_seq1;
drop function if exists ainsert_func;